﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_Old_EntityAttributes]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @strcols AS NVARCHAR(MAX),
@numcols AS NVARCHAR(MAX),
@bitcols AS NVARCHAR(MAX),
@datecols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(MAX),
		@entitysubtype as NVARCHAR(MAX)

		execute('select @entitysubtype = EntitySubType from Entity where EntityID=100001')

	select @strcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from AttributesWithAttributeNames WHERE DataType='String'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @numcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from AttributesWithAttributeNames  WHERE DataType='Number'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @bitcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from AttributesWithAttributeNames  WHERE DataType='Bit'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @datecols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from AttributesWithAttributeNames  WHERE DataType='Date'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')


	set @query = 'SELECT * FROM (SELECT DISTINCT EntityID from AttributesWithAttributeNames) AS mainatt' +
				
				' LEFT OUTER JOIN (SELECT EntityID as EID, ' + @strcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueString)
					for AttributeName in (' + @strcols + ')
				) a WHERE DataType=''' + 'String' + ''') AS stratt ON mainatt.EntityID = stratt.EID ' +

				' LEFT OUTER JOIN (SELECT EntityID as EID ' + @datecols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueDateTime)
					for AttributeName in ( [non], ' + @datecols + ')
				) b WHERE DataType=''' + 'Date' + ''') AS dateatt ON mainatt.EntityID = dateatt.EID' +

				' LEFT OUTER JOIN (SELECT EntityID as EID ' + @numcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueNumber)
					for AttributeName in ( [non],' + @numcols + ')
				) c WHERE DataType=''' + 'Number' + ''') AS numatt ON mainatt.EntityID = numatt.EID' 
				

				
				


	execute(@query)   
END
